﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BusinessLibrary.BLL;
using System.Data.SqlClient;

namespace BusinessLibrary.DAL
{
    class sqlEnseignant
    {
        private static Enseignant ReaderToObject(SqlDataReader dr)
        {
            Enseignant _enseignant = new Enseignant();
            _enseignant.Id = int.Parse(dr["Id"].ToString());
            _enseignant.Nom = dr["Nom"].ToString();
            _enseignant.Prenom = dr["Prenom"].ToString();
            _enseignant.Num_tel = dr["Num_tel"].ToString();
            _enseignant.Email = dr["Email"].ToString();
            _enseignant.Date_nais = DateTime.Parse( dr["Date_nais"].ToString());
            _enseignant.Lieu_nais = dr["Lieu_nais"].ToString();
            _enseignant.Nationalite = dr["Nationalite"].ToString();
            _enseignant.Diplome = dr["Diplome"].ToString();
            
            //_enseignant.Nbr_heure_cours = int.Parse(dr["Nbr_heure_cours"].ToString());
            if (!object.ReferenceEquals(dr["Grade"], DBNull.Value))
            {
                _enseignant.Nbr_heure_tp =int.Parse( dr["Nbr_heure_tp"].ToString());
            }
            //_enseignant.Sex = dr["Sex"].ToString();
            //_enseignant.Score = dr["Score"].ToString();


            if (!object.ReferenceEquals(dr["Grade"], DBNull.Value)){
                _enseignant.Grade = int.Parse(dr["Grade"].ToString()); 
            }

             if (!object.ReferenceEquals(dr["Nbr_heure_cours"], DBNull.Value)){
                _enseignant.Nbr_heure_cours = int.Parse(dr["Nbr_heure_cours"].ToString()); 
            }

             if (!object.ReferenceEquals(dr["Sex"], DBNull.Value)){
                _enseignant.Sex = dr["Sex"].ToString(); 
                 
            }

             if (!object.ReferenceEquals(dr["Score"], DBNull.Value)){
                _enseignant.Cin = int.Parse(dr["Score"].ToString()); 
            }
             
            if (!object.ReferenceEquals(dr["Cin"], DBNull.Value)){
                _enseignant.Cin = int.Parse(dr["Cin"].ToString()); 
            }

            if (!object.ReferenceEquals(dr["Id_authentification"], DBNull.Value)){
                _enseignant.authentification = Authentification.GetAuthentificationById(int.Parse(dr["Id_authentification"].ToString()));
            }

             if (!object.ReferenceEquals(dr["Id_pays"], DBNull.Value)){
                _enseignant.pays = Pays.GetPaysById(int.Parse(dr["Id_pays"].ToString()));
            }

              if (!object.ReferenceEquals(dr["Id_gouvernorat"], DBNull.Value)){
                _enseignant.gouvernorat = Gouvernorat.GetGouvernoratById(int.Parse(dr["Id_gouvernorat"].ToString()));
            }

               if (!object.ReferenceEquals(dr["Id_ville"], DBNull.Value)){
                _enseignant.ville = Ville.GetVilletById (int.Parse(dr["Id_ville"].ToString()));
            }

               if (!object.ReferenceEquals(dr["Id_specialite"], DBNull.Value)){
                _enseignant.specialite = Specialite.GetSpecialiteById(int.Parse(dr["Id_specialite"].ToString()));
            }

                if (!object.ReferenceEquals(dr["Id_departement"], DBNull.Value)){
                _enseignant.departement = Departement.GetDepartementById(int.Parse(dr["Id_departement"].ToString()));
            }


            return _enseignant;
        }

        public static Enseignant GetEnseignantById(int id)
        {
            Enseignant enseignant = new Enseignant();
            string strSQL = @"select * from Enseignant where Id=@Id";
            try{
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString)){
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                    cmd.Parameters.AddWithValue("@Id", id);
                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read()){
                        enseignant = ReaderToObject(dr);
                    }
                    dr.Close();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return enseignant;
        }

        public static int insertEnseignant(Enseignant e)
        {
            int n = 0;
            string strSQL = @"insert into Enseignant (Nom,Prenom,CIN,Num_tel,Email,Date_nais,Lieu_nais,Nationalite,Diplome,Grade" +
            ",Nbr_heure_cours,Nbr_heure_tp,Sex,Score, Id_pays, Id_gouvernorat, Id_ville, Id_authentification, Id_specialite,Id_departement)"
            +"values(@nom,@prenom,@cin,@num_tel,@email,@date_nais,@lieu_nais,@nationalite,@diplome,@grade,@nbr_heure_cours,"+
            "@nbr_heure_tp,@sex,@score,@id_pays,@id_gouvernorat,@id_ville,@id_authentification,@id_specialite, @id_departement)";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString)){
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                    cmd.Parameters.AddWithValue("@cin", e.Cin);
                    cmd.Parameters.AddWithValue("@nom", e.Nom);
                    cmd.Parameters.AddWithValue("@prenom", e.Prenom);
                    cmd.Parameters.AddWithValue("@num_tel", e.Num_tel);
                    cmd.Parameters.AddWithValue("@email", e.Email);
                    cmd.Parameters.AddWithValue("@date_nais", e.Date_nais);
                    cmd.Parameters.AddWithValue("@lieu_nais", e.Lieu_nais);
                    cmd.Parameters.AddWithValue("@nationalite", e.Nationalite);
                    cmd.Parameters.AddWithValue("@diplome", e.Diplome);
                    cmd.Parameters.AddWithValue("@grade", e.Grade);
                    cmd.Parameters.AddWithValue("@nbr_heure_cours", e.Nbr_heure_cours);
                    cmd.Parameters.AddWithValue("@nbr_heure_tp", e.Nbr_heure_tp);
                    cmd.Parameters.AddWithValue("@sex", e.Sex);
                    cmd.Parameters.AddWithValue("@score", e.Score);

                    cmd.Parameters.AddWithValue("@id_pays", e.pays.Id);
                    cmd.Parameters.AddWithValue("@id_gouvernorat", e.gouvernorat.Id);
                    cmd.Parameters.AddWithValue("@id_ville", e.ville.Id);
                    cmd.Parameters.AddWithValue("@id_authentification", e.authentification.Id);
                    cmd.Parameters.AddWithValue("@id_specialite", e.specialite.Id);
                    cmd.Parameters.AddWithValue("@id_departement", e.departement.Id);
                    n = cmd.ExecuteNonQuery();
                    cnx.Close();                    
                }
            }
            catch (Exception) { }
            return n;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public static int updateEnseignant(Enseignant e)
        {
             int n = 0;

             string strSQl = @"UPDATE Enseignant SET (@nom IS NULL OR Nom=@nom), (@prenom IS NULL OR Prenom=@prenom),"+
             "(@cin IS NULL OR Cin=@cin),(@num_tel IS NULL OR Num_tel=@num_tel),(@email IS NULL OR Email=@email), "+
             "(@date_nais IS NULL OR Date_nais=@date_nais),(@Lieu_nais IS NULL OR Lieu_nais=@lieu_nais), "+
             "(@nationalite IS NULL OR Nationalite=@nationalite),(@diplome IS NULL OR Diplome=@diplome), "+
             "(@grade IS NULL OR Grade=@grade),(@nbr_heure_cours IS NULL OR Nbr_heure_cours=@nbr_heure_cours), "+
             "(@nbr_heure_tp IS NULL OR Nbr_heure_tp=@nbr_heure_tp),(@sex IS NULL OR Sex=@sex), (@score IS NULL OR Score=@score), "+
             "Id_pays=@id_pays, Id_gouvernorat=@id_gouvernorat, Id_ville=@id_ville, Id_authentification=@id_authentification,"+
             "Id_specialite=@id_specialite, Id_departement=@id_departement where Id=@id ";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    
                     SqlCommand cmd = new SqlCommand(strSQl, cnx);
                     cmd.Parameters.AddWithValue("@cin", e.Cin);
                    cmd.Parameters.AddWithValue("@nom", e.Nom);
                    cmd.Parameters.AddWithValue("@prenom", e.Prenom);
                    cmd.Parameters.AddWithValue("@num_tel", e.Num_tel);
                    cmd.Parameters.AddWithValue("@email", e.Email);
                    cmd.Parameters.AddWithValue("@date_nais", e.Date_nais);
                    cmd.Parameters.AddWithValue("@lieu_nais", e.Lieu_nais);
                    cmd.Parameters.AddWithValue("@nationalite", e.Nationalite);
                    cmd.Parameters.AddWithValue("@diplome", e.Diplome);
                    cmd.Parameters.AddWithValue("@grade", e.Grade);
                    cmd.Parameters.AddWithValue("@nbr_heure_cours", e.Nbr_heure_cours);
                    cmd.Parameters.AddWithValue("@nbr_heure_tp", e.Nbr_heure_tp);
                    cmd.Parameters.AddWithValue("@sex", e.Sex);
                    cmd.Parameters.AddWithValue("@score", e.Score);

                    cmd.Parameters.AddWithValue("@id_pays", e.pays.Id);
                    cmd.Parameters.AddWithValue("@id_gouvernorat", e.gouvernorat.Id);
                    cmd.Parameters.AddWithValue("@id_ville", e.ville.Id);
                    cmd.Parameters.AddWithValue("@id_authentification", e.authentification.Id);
                    cmd.Parameters.AddWithValue("@id_specialite", e.specialite.Id);
                    cmd.Parameters.AddWithValue("@id_departement", e.departement.Id);            
                    n= cmd.ExecuteNonQuery();
                    
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return n;
        }
       


        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="idAuthentif"></param>
        /// <returns></returns>
        public static int deletEnseignant(int id, int idAuthentif)
        { 
            int n = 0;
            string strSQL = @"delete from Enseignant where id=@id)"; 
            string strSQl2=@"UPDATE Authentification SET ACTIF='0' where id=@idAuth and ACTIF=1";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                     SqlCommand cmd2 = new SqlCommand(strSQl2, cnx);
                    cmd.Parameters.AddWithValue("@id", id);                   
                    cmd2.Parameters.AddWithValue("@idAuth", idAuthentif);
                    if(cmd.ExecuteNonQuery()>0) 
                      n=  cmd2.ExecuteNonQuery();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return n;
        }

    }
    }
